/**
 * Copyright &copy; 2016-2017 <a href="http://git.oschina.net/whoamien/backend_management">Backend Management</a> All rights reserved.
 */
package com.xw.app.generator.service;

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.stream.Collectors;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.hibernate.engine.spi.SessionImplementor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.xw.app.generator.util.GenerateUtil;
import com.xw.app.generator.vo.Column;
import com.xw.app.generator.vo.GeneratorSelection;
import com.xw.app.generator.vo.KeyColumn;
import com.xw.app.generator.vo.Table;
import com.xw.app.model.GeneratorConfig;
import com.xw.app.service.GeneratorConfigService;
import com.xw.app.vo.CodeGenerateResult;
import com.xw.util.FileUtil;
import com.xw.util.StringUtil;

/**
 * The Class CodeGeneratorServiceImpl.
 *
 * @author Xi Wei
 */
@Service
@Transactional(readOnly = true)
public class CodeGeneratorServiceImpl implements CodeGeneratorService {

	@PersistenceContext(unitName="schemaEntityManagerFactory")
	private EntityManager em;

	@Value("${schema_jdbc_schema}")
	private String jdbcSchema;
	
	@Autowired
	private GeneratorConfigService generatorConfigService;

	
	private Table generateTable(ResultSet result, boolean setTableInColumn){
		try {
			Table table = new Table();
			table.setTableCatalog(result.getString("TABLE_CAT"));
			table.setTableComment(result.getString("REMARKS"));
			table.setTableName(result.getString("TABLE_NAME"));
			table.setTableSchema(result.getString("TABLE_SCHEM"));
			table.setColumnList(listColumns(table.getTableName()));				
			table.setPrimaryKeyList(listPrimaryKeys(table.getTableName()));
			table.setImportedKeyList(listImportedKeys(table.getTableName()));
			table.setExportedKeyList(listExportedKeys(table.getTableName()));	
			if(setTableInColumn){
				table.getColumnList().forEach(x -> x.setTable(table));				
			}
			table.getImportedKeyList().forEach(x -> {
				table.getColumnList().stream().filter(c -> c.getColumnName().equals(x.getColumnName())).findFirst().get().setReferenceColumn(x);
			});

			return table;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	private Column generateColumn(ResultSet result){
		
		try {
			Column column = new Column();
			column.setColumnSize(result.getInt("COLUMN_SIZE"));
			column.setCharacterOctetLength(result.getInt("CHAR_OCTET_LENGTH"));
			column.setColumnComment(result.getString("REMARKS"));
			column.setColumnDefault("COLUMN_DEF");
			column.setColumnName(result.getString("COLUMN_NAME"));
			column.setSqlType(result.getInt("DATA_TYPE"));
			column.setDataType(result.getString("TYPE_NAME"));
			
			if("YES".equalsIgnoreCase(result.getString("IS_NULLABLE"))){
				column.setNullable(true);
			}else{
				column.setNullable(false);
			}
			column.setOrdinalPosition(result.getInt("ORDINAL_POSITION"));
			column.setTableName(result.getString("TABLE_NAME"));
			column.setTableSchema(result.getString("TABLE_SCHEM"));
			
			column.initColumnDefInfo();
			if("view_config".equals(column.getColumnName())){
				System.out.println(jdbcSchema);
			}
			return column;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/* (non-Javadoc)
	 * @see com.xw.app.generator.service.CodeGeneratorService#getTable(java.lang.String, boolean)
	 */
	@Override
	public Table getTable(String tableName,boolean getColumnAndKey){
		
		SessionImplementor sessionImp = (SessionImplementor) em.getDelegate();
		
		Connection connection = sessionImp.connection();
		try {

			DatabaseMetaData databaseMetaData = connection.getMetaData();

			try (ResultSet result = databaseMetaData.getTables(connection.getCatalog(), jdbcSchema, tableName, null)) {
				if (result.next()) {
					Table table = generateTable(result,getColumnAndKey);
					return table;
				}else{
					return null;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * Gets the column.
	 *
	 * @param tableName the table name
	 * @param columnName the column name
	 * @return the column
	 */
	public Column getColumn(String tableName,String columnName){
		
		SessionImplementor sessionImp = (SessionImplementor) em.getDelegate();
		Connection connection = sessionImp.connection();
		try {

			DatabaseMetaData databaseMetaData = connection.getMetaData();
			
			try (ResultSet result = databaseMetaData.getColumns(connection.getCatalog(), jdbcSchema, tableName, columnName)) {
				if (result.next()) {
					Column column = generateColumn(result);
					return column;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}
	
	/* (non-Javadoc)
	 * @see com.xw.app.generator.service.CodeGeneratorService#listTables()
	 */
	@Override
	public List<Table> listTables() {
		List<Table> tables = Lists.newArrayList();
		SessionImplementor sessionImp = (SessionImplementor) em.getDelegate();
		
		Connection connection = sessionImp.connection();
		try {

			DatabaseMetaData databaseMetaData = connection.getMetaData();

			try (ResultSet result = databaseMetaData.getTables(connection.getCatalog(), jdbcSchema, null, null)) {
				while (result.next()) {
					Table table = generateTable(result,false);
					if(table.getPrimaryKeyList() == null || table.getPrimaryKeyList().isEmpty()){
						continue;
					}
					tables.add(table);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return tables;
	}

	/* (non-Javadoc)
	 * @see com.xw.app.generator.service.CodeGeneratorService#listColumns(java.lang.String)
	 */
	@Override
	public List<Column> listColumns(String tableName) {
		// List<Columns> columns =
		// columnsDAO.getByTableSchemaAndTableName(SCHEMA, tableName);
		List<Column> columns = Lists.newArrayList();

		SessionImplementor sessionImp = (SessionImplementor) em.getDelegate();
		
		Connection connection = sessionImp.connection();
		try {

			DatabaseMetaData databaseMetaData = connection.getMetaData();
			
			try (ResultSet result = databaseMetaData.getColumns(connection.getCatalog(), jdbcSchema, tableName, null)) {
				while (result.next()) {
					Column column = generateColumn(result);
					String columnName = column.getColumnName();
					if("id".equalsIgnoreCase(columnName) || "create_date".equalsIgnoreCase(columnName) ||
							"create_user_id".equalsIgnoreCase(columnName) || "update_date".equalsIgnoreCase(columnName) ||
							"update_user_id".equalsIgnoreCase(columnName)){
						continue;
					}
					columns.add(column);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return columns;
	}
	
	/* (non-Javadoc)
	 * @see com.xw.app.generator.service.CodeGeneratorService#listPrimaryKeys(java.lang.String)
	 */
	@Override
	public List<KeyColumn> listPrimaryKeys(String tableName) {
		
		List<KeyColumn> primaryKeys = Lists.newArrayList();
		
		SessionImplementor sessionImp = (SessionImplementor) em.getDelegate();
		
		Connection connection = sessionImp.connection();
		try {

			DatabaseMetaData databaseMetaData = connection.getMetaData();

			try (ResultSet result = databaseMetaData.getPrimaryKeys(connection.getCatalog(), jdbcSchema, tableName)) {
				while (result.next()) {
					KeyColumn key = new KeyColumn();
					key.setColumnName("COLUMN_NAME");
					
					primaryKeys.add(key);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return primaryKeys;
	}

	/**
	 * 获取表的外键列引用的主键列的信息.
	 *
	 * @param tableName the table name
	 * @return the list
	 */
	@Override
	public List<KeyColumn> listImportedKeys(String tableName) {
		List<KeyColumn> importedKeys = Lists.newArrayList();
		
		SessionImplementor sessionImp = (SessionImplementor) em.getDelegate();
		
		Connection connection = sessionImp.connection();
		try {

			DatabaseMetaData databaseMetaData = connection.getMetaData();

			try (ResultSet result = databaseMetaData.getImportedKeys(connection.getCatalog(), jdbcSchema, tableName)) {
				while (result.next()) {
					KeyColumn key = new KeyColumn();
					key.setTableName(tableName);
					key.setColumnName(result.getString("FKCOLUMN_NAME"));
					key.setConstraintName(result.getString("FK_NAME"));
					key.setReferencedColumnName(result.getString("PKCOLUMN_NAME"));
					key.setReferencedTableName(result.getString("PKTABLE_NAME"));
					
					key.setColumn(getColumn(tableName, key.getColumnName()));
					key.setReferencedColumn(getColumn(key.getReferencedTableName(), key.getReferencedColumnName()));
					key.setReferencedColumnList(listColumns(key.getReferencedTableName()));
					importedKeys.add(key);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return importedKeys;
	}
	
	/**
	 * 获取表的主键列的外键列描述.
	 *
	 * @param tableName the table name
	 * @return the list
	 */
	@Override
	public List<KeyColumn> listExportedKeys(String tableName) {
		List<KeyColumn> exportedKeys = Lists.newArrayList();
		
		SessionImplementor sessionImp = (SessionImplementor) em.getDelegate();
		
		Connection connection = sessionImp.connection();
		try {

			DatabaseMetaData databaseMetaData = connection.getMetaData();

			try (ResultSet result = databaseMetaData.getExportedKeys(connection.getCatalog(), jdbcSchema, tableName)) {
				while (result.next()) {
					KeyColumn key = new KeyColumn();
					key.setColumnName(result.getString("PKCOLUMN_NAME"));
					key.setConstraintName(result.getString("FK_NAME"));
					key.setReferencedColumnName(result.getString("FKCOLUMN_NAME"));
					key.setReferencedTableName(result.getString("FKTABLE_NAME"));
					
					key.setReferencedColumnList(listColumns(key.getReferencedTableName()));
					exportedKeys.add(key);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return exportedKeys;
	}
	
	
	/* (non-Javadoc)
	 * @see com.xw.app.generator.service.CodeGeneratorService#generate(com.xw.app.generator.vo.GeneratorSelection)
	 */
	@Override
	public CodeGenerateResult generate(GeneratorSelection generatorSelection)
	{
		Map<String,Object> model = Maps.newHashMap();
		
		List<GeneratorConfig> configList = generatorConfigService.findAll();
		configList.forEach(config -> model.put(config.getConfigName(), config.getConfigValue()));
		
		//Get the columns to use the Data Dict
		String[] dictCodes = generatorSelection.getDictCodes();
		Map<String,Map<String,String>> dictCodeMap = Maps.newHashMap();
		if(dictCodes != null){
			Arrays.asList(dictCodes).forEach(x -> {
				String[] temp = x.split("\\|\\|");
				if(temp.length == 3){
					if(dictCodeMap.get(temp[0]) != null){
						dictCodeMap.get(temp[0]).put(temp[1], temp[2]);
					}else{
						Map<String,String> tempMap = Maps.newHashMap();
						tempMap.put(temp[1], temp[2]);
						dictCodeMap.put(temp[0], tempMap);
					}					
				}
			});
		}
		
		//Get the columns having foreign and it's corresponding display name
		Map<String,Map<String,String>> foreignKeyDisplayColumnMap = Maps.newHashMap();
		String[] foreignKeyDisplayColumns = generatorSelection.getForeignKeyDisplayColumn();
		if(foreignKeyDisplayColumns != null){
			Arrays.asList(foreignKeyDisplayColumns).forEach(x -> {
				String[] temp = x.split("\\|\\|");
				if(temp.length == 4){
					if(foreignKeyDisplayColumnMap.get(temp[0]) != null){
						foreignKeyDisplayColumnMap.get(temp[0]).put(temp[1], StringUtil.toCamelCase(temp[3]));
					}else{
						Map<String,String> tempMap = Maps.newHashMap();
						tempMap.put(temp[1], StringUtil.toCamelCase(temp[3]));
						foreignKeyDisplayColumnMap.put(temp[0], tempMap);
					}
				}
			});
		}
		model.put("basepackage_dir", ((String)model.get("basepackage")).replaceAll("\\.", Matcher.quoteReplacement(File.separator)));
		String templateFolder = generatorSelection.getTemplateFolder() + File.separator + model.get("templatefolder");
		
		FileUtil.deleteDir((String)model.get("outputDir"));
		
		for(String tableName : generatorSelection.getTableNames()){
			//set the dataDictCode if the column use the Data Dict
			Table table = getTable(tableName, true);
			model.put("table", table);
			model.put("ClassName", table.getTableClassName());
			Map<String,String> dictMap = dictCodeMap.get(tableName);
			if(dictMap != null && !dictMap.isEmpty()){
				for(String key : dictMap.keySet())
				{
					String value = dictMap.get(key);
					table.getColumnList().stream().filter(x -> x.getColumnName().equalsIgnoreCase(key)).findFirst().get().setDictCode(value);
				}
			}
			
			//set the referencedDisplayName of the KeyColumn if the column have the foreign key
			Map<String,String> referenceMap = foreignKeyDisplayColumnMap.get(tableName);
			if(referenceMap != null && !referenceMap.isEmpty())
			{
				for(String key : referenceMap.keySet()){
					String value = referenceMap.get(key);
					table.getColumnList().stream().filter(x -> x.getColumnName().equalsIgnoreCase(key)).findFirst().get().getReferenceColumn().setReferencedDisplayName(value);
				}
			}
			
			//set the columns to be used in query panel
			List<String> queryColumns = generatorSelection.getSearchColumns().get(tableName);
			if(queryColumns != null && !queryColumns.isEmpty())
			{
				table.getColumnList().stream().filter(x -> queryColumns.contains(x.getColumnName())).collect(Collectors.toCollection(() -> table.getSearchColumnList()));
				
			}else{
				table.setSearchColumnList(table.getColumnList());
			}
			
			//set the columns to be used in List tble
			List<String> listColumns = generatorSelection.getListColumns().get(tableName);
			if(listColumns != null && !listColumns.isEmpty()){
				table.getColumnList().stream().filter(x -> listColumns.contains(x.getColumnName())).collect(Collectors.toCollection(()->table.getListColumnList()));
			}else{
				table.setListColumnList(table.getColumnList());
			}
			
			//set the columns to be used in the Add/Edit page
			List<String> editColumns = generatorSelection.getEditColumns().get(tableName);
			if(editColumns != null && !editColumns.isEmpty()){
				table.getColumnList().stream().filter(x -> editColumns.contains(x.getColumnName())).collect(Collectors.toCollection(()->table.getEditColumnList()));
			}else{
				table.setEditColumnList(table.getColumnList());
			}
				
			
			try {
				CodeGenerateResult result = GenerateUtil.scanTemplatesAndProcess(new File(templateFolder),model);
				return result;
				
			} catch (Exception e) {
				e.printStackTrace();
			}
			
		}
		
		
		return null;
	}

}
